package model.dba;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.List;

import model.Alumno;

public class AlumnoDAO extends JDBC {
	private static AlumnoDAO alumnoDAO;

	private AlumnoDAO() {
		super();
	}

	public static AlumnoDAO getInstance() {
		if (alumnoDAO == null) {
			alumnoDAO = new AlumnoDAO();
		}
		return alumnoDAO;
	}

	public int add(Alumno alumno) {

		try {
			String queryString = "INSERT INTO alumnos( dni, nombre, apellido, direccion, tel, cel, fechaNac)" + " VALUES(?, ?, ?, ?, ?, ?, ?) ";

			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setString(1, alumno.getDNI());
			preparedStatement.setString(2, alumno.getNombre());
			preparedStatement.setString(3, alumno.getApellido());
			preparedStatement.setString(4, alumno.getEmail());
			preparedStatement.setString(5, alumno.getTelefono());
			preparedStatement.setString(6, alumno.getCelular());
			preparedStatement.setTimestamp(7, new Timestamp(alumno.getNacimiento().getTimeInMillis()));

			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return getLastId();

	}

	// (idAlumno, dni, nombre, apellido, direccion, tel, cel, fechaNac)

	// insert into alumnos
	// values(1,38795744,'Graimito','Gonzalez','ggonzalez@chaja.com','011-49875970','1115759997','1989/09/30','no');

	public boolean update(Alumno alumno) {
		boolean state = false;
		try {
			String queryString = "UPDATE alumnos SET  dni=?, nombre=?, apellido=?, direccion=?, tel=?, cel=?, fechaNac = ? WHERE idAlumno=?";
			preparedStatement.setString(1, alumno.getDNI());
			preparedStatement.setString(2, alumno.getNombre());
			preparedStatement.setString(3, alumno.getApellido());
			preparedStatement.setString(4, alumno.getEmail());
			preparedStatement.setString(5, alumno.getTelefono());
			preparedStatement.setString(6, alumno.getCelular());
			preparedStatement.setTimestamp(7, new Timestamp(alumno.getNacimiento().getTimeInMillis()));
			preparedStatement.setInt(8, alumno.getId());
			preparedStatement = connection.prepareStatement(queryString);
			// id va ultimo

			state = preparedStatement.executeUpdate() != 0 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return state;
	}

	public boolean delete(Alumno alumno) {
		boolean state = false;
		try {
			String queryString = "DELETE FROM alumnos WHERE idAlumno=?";

			preparedStatement = connection.prepareStatement(queryString);
			preparedStatement.setInt(1, alumno.getId());
			state = preparedStatement.executeUpdate() != 0 ? true : false;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return state;

	}

	public List<Alumno> getAlumno(String nombre, String apellido, String telefono, String email) {

		List<Alumno> alumnoList = new ArrayList<Alumno>();
		try {
			String queryString = "SELECT * FROM alumnos where nombre like '" + nombre + "%' and apellido like '" + apellido + "%' and tel like '" + telefono + "%' and direccion like'" + email + "%';";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				alumnoList.add(populate());

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return alumnoList;
	}

	public Alumno getAlumno(int id) {

		Alumno alumno = new Alumno();
		try {
			String queryString = "SELECT * FROM alumnos where idAlumno = '" + id + "';";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				alumno = populate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return alumno;
	}

	public int getLastId() {
		int ret = 0;
		try {
			String queryString = "SELECT idAlumno FROM alumnos order by idAlumno DESC LIMIT 1  ";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				ret = resultSet.getInt("idAlumno");

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return ret;

	}

	public List<Alumno> getAll() {
		List<Alumno> alumnoList = new ArrayList<Alumno>();

		try {
			String queryString = "SELECT * FROM alumnos  ;";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				alumnoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return alumnoList;
	}

	public List<Alumno> find(String nombre, String apellido, String direccion, String telefono) {
		List<Alumno> alumnoList = new ArrayList<Alumno>();

		try {
			String queryString = "SELECT * FROM alumnos  WHERE lower(nombre) like lower('" + nombre + "%') and lower(apellido) like  lower('" + apellido + "%') and lower(direccion) like  lower('" + direccion + "%') and lower(tel) like  lower('" + telefono + "%');";

			preparedStatement = connection.prepareStatement(queryString);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				alumnoList.add(populate());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (preparedStatement != null)
					preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return alumnoList;
	}

	private Alumno populate() throws SQLException {

		Alumno alumno = new Alumno();
		alumno.setApellido(resultSet.getString("apellido"));
		alumno.setCelular(resultSet.getString("cel"));
		alumno.setEmail(resultSet.getString("direccion"));
		alumno.setDNI(resultSet.getString("dni"));
		GregorianCalendar nacimiento = new GregorianCalendar();
		nacimiento.setTime(resultSet.getTimestamp("fechaNac"));
		alumno.setNacimiento(nacimiento);
		alumno.setNombre(resultSet.getString("nombre"));
		alumno.setTelefono(resultSet.getString("tel"));
		alumno.setId((int) resultSet.getLong("idAlumno"));

		return alumno;

	}
}